package blob;

import bean.Customer;
import org.junit.Test;
import util.JDBCUtils;

import java.io.*;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/*
* 测试使用PreparedStatement操作Blob类型的数据
* */
public class BlobTest {

    //向数据表customers中插入Blob类型的字段
    @Test
    public void testInsertBlob() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //连接数据库
            connection = JDBCUtils.getConnection();
            //预编译SQL语句，返回一个preparedStatement实例
            String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            preparedStatement.setObject(1, "南拳妈妈");
            preparedStatement.setObject(2, "namquanmama@gmail");
            preparedStatement.setObject(3, "2004-01-01");
            FileInputStream fis = new FileInputStream(new File("src/images/girl.jpg"));
            preparedStatement.setBlob(4, fis);
            //执行
            preparedStatement.execute();
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            //资源的关闭
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }

    }


    //查询数据表customers中Blob类型的字段
    @Test
    public void testQueryBlob() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        InputStream is = null;
        OutputStream os = null;
        try {
            connection = JDBCUtils.getConnection();

            String sql = "select id,name,email,birth,photo from customers where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, 19);

            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                //方式一 resultSet.getObject(number)  number为列的顺序
                /*int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);*/
                //方式二 resultSet.getObject("列名")
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");

                //将Blob类型的字段下载下来，以文件的方式保存在本地
                Blob photo = resultSet.getBlob("photo");
                is = photo.getBinaryStream();
                os = new FileOutputStream("src/images/mei.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while ((len = is.read(buffer)) != -1) {
                    os.write(buffer, 0, len);
                }

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer.toString());
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            //资源的关闭
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }

    }
}
